package com.news.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.news.bean.News;
import com.news.dao.NewsDao;
import com.news.util.DButil;
import org.apache.commons.fileupload.FileItem;

public class NewsDaoImpl implements NewsDao {

    private final String ADD = "insert into news values(null,?,?,?,?,?,?,?,?,?)";
    private final String LIST = "select * from news";
    private final String EDIT = "update news set title=?,sort=?,writer=?," +
            "dates=?,shows=?,message=?,hits=? where id=?";
    private final String DEL = "delete from news where id=?";
    private final String TOTAL = "select count(*) from news where id=?";
    private final String GET = "select * from news where id=?";
    private final String LISTKEY = "select * from news where  title like ?";
    private final String GETBYSORT = "select * from news where sort=?";
    private final String LASTEST="SELECT * from news ORDER BY dates DESC LIMIT 5";

    public int add(News news) {
        int ret = 0, i = 1;
        PreparedStatement pstmt = DButil.getPreparedStatement(ADD);
        try {
            pstmt.setString(i++, news.getTitle());
            pstmt.setString(i++, news.getPic());
            pstmt.setInt(i++, news.getSort());
            pstmt.setString(i++, news.getWriter());
            pstmt.setString(i++, news.getDates());
            pstmt.setString(i++, news.getShows() == null ? "0" : news.getShows());//(i++, is.isShow());
            pstmt.setBytes(i++, news.getMessage().getBytes());
            pstmt.setInt(i++, news.getHits());
            if (news.getPicFile() != null) {
//                pstmt.setBinaryStream(i++, news.getPicFile().getInputStream(), news.getPicFile().getInputStream().available());
                pstmt.setBytes(i++, news.getPicFile());
            }else{
                pstmt.setBytes(i++, null);
            }
            ret = pstmt.executeUpdate();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DButil.close();
        }
        return ret;
    }

    public List<News> list(Map<String, String> map) {
        List<News> list = new ArrayList<News>();
        try {
            PreparedStatement pstmt = DButil.getPreparedStatement(LIST);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                News news = new News();
                news.setId(rs.getInt("id"));
                news.setTitle(rs.getString("title"));
                news.setPic(rs.getString("pic"));
                news.setSort(rs.getInt("sort"));
                news.setWriter(rs.getString("writer"));
                news.setDates(rs.getString("dates"));
                news.setShows(rs.getString("shows"));
                news.setHits(rs.getInt("hits"));
                news.setMessage(new String(rs.getBytes("message"),"UTF-8"));
                list.add(news);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DButil.close();
        }
        return list;
    }

    public List<News> list() {
        List<News> list = new ArrayList<News>();
        try {
            PreparedStatement pstmt = DButil.getPreparedStatement(LIST);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                News news = new News();
                news.setId(rs.getInt("id"));
                news.setTitle(rs.getString("title"));
                news.setPic(rs.getString("pic"));
                news.setSort(rs.getInt("sort"));
                news.setWriter(rs.getString("writer"));
                news.setDates(rs.getString("dates"));
                news.setShows(rs.getString("shows"));
                news.setHits(rs.getInt("hits"));
                news.setMessage(new String(rs.getBytes("message"),"UTF-8"));
                list.add(news);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DButil.close();
        }
        return list;
    }
   
     public List<News> last() {
        List<News> list = new ArrayList<News>();
        try {
            PreparedStatement pstmt = DButil.getPreparedStatement(LASTEST);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                News news = new News();
                news.setId(rs.getInt("id"));
                news.setTitle(rs.getString("title"));
                news.setPic(rs.getString("pic"));
                news.setSort(rs.getInt("sort"));
                news.setWriter(rs.getString("writer"));
                news.setDates(rs.getString("dates"));
                news.setShows(rs.getString("shows"));
                news.setHits(rs.getInt("hits"));
                news.setMessage(rs.getString("message"));
                list.add(news);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DButil.close();
        }
        return list;
    }

    public List<News> getBySort(int sort) {
        List<News> list = new ArrayList<News>();
        try {
            PreparedStatement pstmt = DButil.getPreparedStatement(GETBYSORT);
            pstmt.setInt(1, sort);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                News news = new News();
                news.setId(rs.getInt("id"));
                news.setTitle(rs.getString("title"));
                news.setPic(rs.getString("pic"));
                news.setSort(rs.getInt("sort"));
                news.setWriter(rs.getString("writer"));
                news.setDates(rs.getString("dates"));
                news.setShows(rs.getString("shows"));
                news.setHits(rs.getInt("hits"));
                news.setMessage(new String(rs.getBytes("message"),"UTF-8"));
                list.add(news);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DButil.close();
        }
        return list;
    }

    public int delete(Integer id) {
        int ret = 0;
        PreparedStatement pstmt = DButil.getPreparedStatement(DEL);
        try {
            pstmt.setInt(1, id);
            ret = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButil.close();
        }
        return ret;
    }

    public int edit(News news) {
        int ret = 0, i = 1;
        PreparedStatement pstmt = DButil.getPreparedStatement(EDIT);
        try {
            pstmt.setString(i++, news.getTitle());
            pstmt.setInt(i++, news.getSort());
            pstmt.setString(i++, news.getWriter());
            pstmt.setString(i++, news.getDates());
            pstmt.setString(i++, news.getShows() == null ? "0" : news.getShows());
            pstmt.setBytes(i++, news.getMessage().getBytes());
            pstmt.setInt(i++, news.getHits());
            pstmt.setInt(i++, news.getId());
            ret = pstmt.executeUpdate();
            System.out.println("edit result is "+ret);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            DButil.close();
        }
        return ret;
    }

    public List<News> ListByKeyWord(String keyWord) {
        List<News> list = new ArrayList<News>();
        try {
            PreparedStatement pstmt = DButil.getPreparedStatement(LISTKEY);
            pstmt.setString(1, "%" + keyWord + "%");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                News news = new News();

                news.setId(rs.getInt("id"));
                news.setTitle(rs.getString("title"));
                news.setPic(rs.getString("pic"));
                news.setSort(rs.getInt("sort"));
                news.setWriter(rs.getString("writer"));
                news.setDates(rs.getString("dates"));
                news.setShows(rs.getString("shows"));
                news.setHits(rs.getInt("hits"));
                news.setMessage(new String(rs.getBytes("message"),"UTF-8"));
                list.add(news);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DButil.close();
        }
        return list;
    }

    public News get(Integer id) {
        PreparedStatement pstmt = DButil.getPreparedStatement(GET);
        News news = new News();
        try {
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                news.setId(rs.getInt("id"));
                news.setTitle(rs.getString("title"));
                news.setWriter(rs.getString("writer"));
                String message = new String(rs.getBytes("message"));
                news.setMessage(message);
                news.setHits(rs.getInt("hits"));
                news.setShows(rs.getString("shows"));
                news.setSort(rs.getInt("sort"));
                news.setPic(rs.getString("pic"));
                news.setDates(rs.getString("dates"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DButil.close();
        }
        return news;
    }

    public int total(Map<String, String> map) {
        int ret = 0;
        PreparedStatement pstmt = DButil.getPreparedStatement(TOTAL);
        try {
            pstmt.setString(1, map.get("newsTypeId"));
            ResultSet rs = pstmt.executeQuery();
            rs.next();
            ret = rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButil.close();
        }
        return ret;
    }
}
